Defining a Microsoft SQL Connection

You must create a connection to the Microsoft SQL instance(s) before Tidal Automation can run your Microsoft SQL jobs or monitor database events. These connections also must be licensed before Tidal Automation can use them.

The connection for the Microsoft SQL adapter is defined like other Tidal Automation connections except it requires configuration information unique to Microsoft SQL instances.

You can create connections to one or more Microsoft SQL instances and/or databases. Microsoft SQL access can be managed by limiting a user account to use only certain connections to particular servers. A connection is created through the Connection Definition dialog.

This dialog contains these elements:

Name – Enter a name for this Microsoft SQL adapter connection.
Enabled – Activates or disables (deactivates) the connection, shutting down the adapter.

A job cannot run on an adapter if its connection is disabled. If jobs try to run on a disabled connection, the job enters adapter Disabled status. Jobs already running on an adapter that is disabled (or if the connection to the adapter is lost) run to completion; however, their completion status is not returned until the adapter is enabled.
Use as default for MSSQL Jobs – Click to use the connection as your default connection when setting up future Microsoft SQL jobs.
General – This tab designates a job limit and default runtime user.
MSSQL Connection – This tab provides information to connect to the designated Microsoft SQL instance.
Options – This tab provides configuration options for accessing the Microsoft SQL instance.
Description – This tab describes the connection and provides a convenient place for notes about its purpose and use.

Prerequisites for Kerberos Authentication

Microsoft SQL Server supports the Windows Authentication and SQL Authentication types. Windows Authentication can use either Kerberos or New Technology LAN Manager (NTLM) authentication.

Meet these prerequisites before defining an Microsoft SQL Server connection using Kerberos authentication:

Windows server with Microsoft SQL Server installed should be joined to AD domain.
Ensure the MS SQL server is running using the domain account (not the local account) which is, in turn, configured to user Kerberos authentication.
Configure the MS SQL login for the domain account, including required roles.
Configure the Service Principal Names (SPNs) for MS SQL service. Note that SPNs are usually automatically configured if the SQL Server is running using the domain account.

If the TA Master is running on the Windows machine in the AD domain, the Realm field is optional if your Kerberos configuration's default realm is the same as that of the Windows server. If the Windows server has a local account with the same name as the domain account used to connect to the Microsoft SQL Server, configure the runtime user as shown in the example below:

Domain: TWAKERB REALM: TWAKERB.COM

Windows local account: Administrator

Windows domain account: TWAKERB\Administrator

Configure the runtime user as the domain account name + @REALM and leave the Realm field blank. For the example above, runtime user should be Administrator@TWAKERB.COM.

If the TA Master is running on a Linux or Windows machine not joined to an AD domain, ensure that Domain controller is the first option for the namespace server search.

If additional Kerberos configuration is required, specify the default krb5.conf file location as follows:

Windows: C:\winnt\krb5.ini or C:\windows\krb5.ini

Linux: /etc/krb5.conf
The default krb5.conf file location is applied if it is on the classpath of the TA Master. Alternatively, specify the krb5.conf file location explicitly using JVMARGS property in the adapter service.props file:
```
JVMARGS=-Djava.security.krb5.conf=/usr/krb5.conf
```

Adding a Microsoft SQL Connection

Note: Modifying an adapters connection configuration should only be done when there are no jobs running that utilize the adapter connection. If the connection is created while jobs are active, the jobs may go into an orphan state.

To add a connection:

Click Administration > Connections on the Navigation pane to display the Connections pane.
Click Add or right-click and choose Add Connection > MSSQL Adapter from the context menu to display the MSSQL Connection Definition dialog.
Enter a name for the new connection in the Name field.
Click the Job Limit field of the General tab, and then click the maximum number of concurrent active processes that Tidal Automation should submit to this Microsoft SQL instance and/or database at one time.

Optionally, in the Default Runtime User list, choose the name of a default user for Microsoft SQL jobs.

Only users that have been defined with Microsoft SQL passwords display in this list. The user selected is automatically supplied as the runtime user in the adapter job definitions.
Click the Max Job Output Size (in Kb) field, enter the maximum size of the job output to be shown on the Output tab of the Job Details dialog for the Microsoft SQL Server Adapter.
Enter the timezone where the target application environments are based in the Scheduling Time Zone field. This timezone is managed under the Activities > System Configuration > Timezone tab.
Click the MSSql Connection tab.

This tab defines how to access a Microsoft SQL instance. There are several different ways to connect to a Microsoft SQL instance. SIf you wish to connect to a specific database, you can designate that Microsoft SQL database for the connection.
Enter the name of the default database for this connection in the DB Name field. This is identical to the DatabaseName value in a JDBC connection string. This field is optional. If not specified here, you will need to choose a database for each job and event definition that uses this connection.
Optionally, in the DB Instance field, enter the name of the database instance to connect to (if there are multiple instances installed on the same server).

This field is optional. Leave blank to use the default, unnamed instance.
Optionally, check the User Windows Authenticate checkbox to enable the use Windows Authentication feature.

Note: When using Windows Authentication, verify that the MSSql Server for that instance is running as a domain account. MSSql Server requires AD access for login account verification when the adapter intends to connect to MSSql Server.

Note: If Windows Authentication is checked, the connection user and all runtime users associated with jobs for the connection will be authenticated with Windows Authentication. Similarly, if Windows Authentication is left unchecked, the connection user and all runtime users will be authenticated using SQL Authentication. Mixing the two authentication methods is not allowed for a single connection.

The jTDS JDBC driver is used to support Windows Authentication. You can download this driver from http://jtds.sourceforge.net. To apply the driver:
1. Stop the Master.
2. Unzip the download driver file, then copy the jTDS JDBC driver (jtds.jar) into {TWA_HOME}/services/{CAFA6E81-E29F-4263-8E80-4928FFF416A2}/lib.
3. Set the jtds classpath path in ${TIDAL_HOME}\config\master.props
  
  Example: Classpath=${TIDAL_HOME}\lib\jtds-1.3.1.jar;${CLASSPATH}.
4. Restart the Master.
Check the Encrypt connection checkbox to specify that the SQL Server uses TLS encryption for all the data sent between the client and the server, if the server has a certificate installed. This checkbox is not selected by default.
Click the appropriate Microsoft SQL user in the Connect User list.
Enter the machine name where the Microsoft SQL database is located in the DB Server field.
Specify the port the database instance listens on in the Port field.
Verify the correct version of the database you are connecting to in the DB Version list.
Check the Use Windows Authenticate checkbox to enable the Kerberos Authentication checkbox.
Check the Kerberos Authentication checkbox to define a connection using Kerberos authentication. See Prerequisites for Kerberos Authentication before defining a connection using Kerberos authentication.
Enter the realm for your environment (4000 characters maximum) in the Realm field. This is an optional field.
Check the Trust server Certificate checkbox for the server TLS/SSL certificate to be automatically trusted when the communication layer is encrypted using TLS. This checkbox is enabled when the Encrypt connection checkbox is selected and the Use Windows Authenticate checkbox is not selected.

When this checkbox is disabled, the driver validates the server TLS/SSL certificate. If the server certificate validation fails, the driver reports an error and closes the connection. Make sure that the value passed to the serverName exactly matches the Common Name (CN) or Domain Name System (DNS) name in the Subject Alternate Name (SAN) in the server certificate for a TLS/SSL connection to succeed.

Note: This property is used in combination with the encrypt/authentication properties. This property only affects the server TLS/SSL certificate validation, if the connection uses TLS encryption.
Enter the path (including filename) to the certificate trustStore file in the Trust Store field. The trustStore file contains the list of certificates that the client trusts. This field is enabled when the Encrypt connection checkbox is selected and when the Trust server Certificate checkbox is not selected.

When this property is unspecified or set to null, the driver relies on the trust manager factory's lookup rules to determine which certificate store to use. The default SunX509 TrustManagerFactory tries to locate the trusted material in the search order:
1. A file specified by the javax.net.ssl.trustStore JVM system property.
2. <java-home>/lib/security/jssecacerts file.
3. <java-home>/lib/security/cacerts file.
  
  Note: This property only affects the certificate trustStore lookup, if the connection uses TLS encryption and the trustServerCertificate property is set to false.
Enter the Trust Store Password that is used to check the integrity of the trustStore data. This field is enabled when the Encrypt connection checkbox is selected and when the Trust server Certificate checkbox is not selected.
- If the trustStore property is set, but the trustStorePassword property is not set, the integrity of the trustStore is not checked.
- When the trustStore and trustStorePassword properties are unspecified, the driver uses the JVM system properties, javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword. If the javax.net.ssl.trustStorePassword system property is not specified, the integrity of the trustStore is not checked.
- If the trustStore property is not set, but the trustStorePassword property is set, the JDBC driver uses the file that is specified by the javax.net.ssl.trustStore as a trust store and the integrity of the trust store is checked using the specified trustStorePassword. This setting is needed when the client application does not want to store the password in the JVM system property.
  
  Note: The trustStorePassword property only affects the certificate trustStore lookup, if the connection uses TLS connection and the trustServerCertificate property is set to false.
Enter the host name to be used in validating the SQL Server TLS/SSL certificate in the Host Name In Certificate field. This field is enabled when the Encrypt connection checkbox is selected and when the Trust server Certificate checkbox is not selected.

If the hostNameInCertificate property is unspecified or set to null, the Microsoft JDBC Driver for SQL Server uses the serverName property value on the connection URL as the host name to validate the SQL Server TLS/SSL certificate.

Note: This property is used in combination with the encrypt/authentication properties and the trustServerCertificate property. This property affects the certificate validation, if the connection uses TLS encryption and the trustServerCertificate is set to false. Make sure that the value passed to hostNameInCertificate matches the Common Name (CN) or DNS name in the Subject Alternate Name in the server certificate for a TLS connection to succeed.
Click the Override JDBC Connection URL option if you need to override the JDBC connection string.
Click the Options tab to configure parameters for this connection.

Note: If the DB Server is Azure SQL database, add CONN_TYPE=Azure as the configuration parameter.
Click the Outages tab to define time windows when the adapter will not be available.
Click OK to add the connection to the TA database.

Microsoft SQL Connection Status Light

If the Microsoft SQL connection is not available to the master, the connection status light in the first column of the display is red (or yellow if other adapters are connected to the Master).

The status light indicates the health of the adapter connection to the SQL server.

If the TA Master cannot connect or loses its connection to a Microsoft SQL instance, you will see a red status light next to your Microsoft SQL connection in the Connections pane of the TA Web client.

Note: If the SQL master adapter connection is not available, you will not be able to define Microsoft SQL jobs and extract information on job steps from your Microsoft SQL instance.